﻿<!doctype html>
<html>
<head>
    <title>SpreadJS - Customize Filter</title>
    <meta charset="utf-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />

    <link href="../../../external/spreadjs/css/gcspread.sheets.excel2013white.9.40.20161.0.css" rel="stylesheet" type="text/css" />

    <script src="../../../external/external/jquery-1.8.2.min.js" type="text/javascript"></script>

    <script type="text/javascript" src="../../../external/spreadjs/gcspread.sheets.all.9.40.20161.0.min.js"></script>

    <style type="text/css">
    /*css_begin*/
        label {
            display:inline-block;
            width: 180px;
            margin-right: 12px;
            text-align:right;
        }

        input[type="checkbox"] + label,
        input[type="radio"] + label {
            text-align: left;
            width: auto;
        }

        input[type="text"] {
            width: 170px;
        }

        select {
            width: 174px;
        }

        input[type="button"] {
            width: 130px;
            margin: 0 30px;
        }
    /*css_end*/
    </style>

    <script id="scriptInit" type="text/javascript">
        /*code_begin*/
        var spreadNS = GcSpread.Sheets;

        $(document).ready(function () {
            var spread = new GcSpread.Sheets.Spread(document.getElementById("ss"));
            initSpread(spread);
        });

        function initSpread(spread) {
            $("#Condition1").change(function () {
                var condition = $("#Condition1").val();
                var type = $("#optEnumType1");
                setEnumType(condition, type);
            });
            $("#Condition2").change(function () {
                var condition = $("#Condition2").val();
                var type = $("#optEnumType2");
                setEnumType(condition, type);
            });

            function setEnumType(condition, type) {
                switch (condition) {
                    case "0":
                        type.empty();
                        type.append("<option value='0'>EqualsTo</option>");
                        type.append("<option value='1'>NotEqualsTo</option>");
                        type.append("<option value='2'>GreaterThan</option>");
                        type.append("<option value='3'>GreaterThanOrEqualsTo</option>");
                        type.append("<option value='4'>LessThan</option>");
                        type.append("<option value='5'>LessThanOrEqualsTo</option>");
                        break;
                    case "1":
                        type.empty();
                        type.append("<option value='0'>EqualsTo</option>");
                        type.append("<option value='1'>NotEqualsTo</option>");
                        type.append("<option value='2'>GreaterThan</option>");
                        type.append("<option value='3'>GreaterThanOrEqualsTo</option>");
                        type.append("<option value='4'>LessThan</option>");
                        type.append("<option value='5'>LessThanOrEqualsTo</option>");
                        break;
                    case "2":
                        type.empty();
                        type.append("<option value='0'>EqualsTo</option>");
                        type.append("<option value='1'>NotEqualsTo</option>");
                        type.append("<option value='2'>BeginsWith</option>");
                        type.append("<option value='3'>DoesNotBeginWith</option>");
                        type.append("<option value='4'>EndsWith</option>");
                        type.append("<option value='5'>DoesNotEndWith</option>");
                        type.append("<option value='6'>Contains</option>");
                        type.append("<option value='7'>DoesNotContain</option>");
                        break;
                    case "3":
                        type.empty();
                        type.append("<option value='0'>BackgroundColor</option>");
                        type.append("<option value='1'>ForegroundColor</option>");
                        break;
                    case "4":
                        type.empty();
                        type.append("<option value='0'>Empty</option>");
                        type.append("<option value='1'>NonEmpty</option>");
                        type.append("<option value='2'>Error</option>");
                        type.append("<option value='3'>NonError</option>");
                        type.append("<option value='4'>Formula</option>");
                        break;
                    case "5":
                        type.empty();
                        type.append("<option value='0'>EqualsTo</option>");
                        type.append("<option value='1'>NotEqualsTo</option>");
                        type.append("<option value='2'>Before</option>");
                        type.append("<option value='3'>BeforeEqualsTo</option>");
                        type.append("<option value='4'>After</option>");
                        type.append("<option value='5'>AfterEqualsTo</option>");
                        break;
                    case "6":
                        type.empty();
                        type.append("<option value='0'>Today</option>");
                        type.append("<option value='1'>Yesterday</option>");
                        type.append("<option value='2'>Tomorrow</option>");
                        type.append("<option value='3'>Last7Days</option>");
                        type.append("<option value='4'>ThisMonth</option>");
                        type.append("<option value='5'>LastMonth</option>");
                        type.append("<option value='6'>NextMonth</option>");
                        type.append("<option value='7'>ThisWeek</option>");
                        type.append("<option value='8'>LastWeek</option>");
                        type.append("<option value='9'>NextWeek</option>");
                        type.append("<option value='10'>fromDay</option>");
                        type.append("<option value='11'>fromMonth</option>");
                        type.append("<option value='12'>fromQuarter</option>");
                        type.append("<option value='13'>fromWeek</option>");
                        type.append("<option value='14'>fromYear</option>");
                        break;
                    case "7":
                        type.empty();
                        type.append("<option value='0'>EqualsTo</option>");
                        type.append("<option value='1'>NotEqualsTo</option>");
                        type.append("<option value='2'>GreaterThan</option>");
                        type.append("<option value='3'>GreaterThanOrEqualsTo</option>");
                        type.append("<option value='4'>LessThan</option>");
                        type.append("<option value='5'>LessThanOrEqualsTo</option>");
                        break;
                    case "8":
                        type.empty();
                        type.append("<option value='0'>Top</option>");
                        type.append("<option value='1'>Bottom</option>");
                        break;
                    default:
                        type.empty();
                        type.append("<option value='0'>EqualsTo</option>");
                        type.append("<option value='1'>NotEqualsTo</option>");
                        type.append("<option value='2'>GreaterThan</option>");
                        type.append("<option value='3'>GreaterThanOrEqualsTo</option>");
                        type.append("<option value='4'>LessThan</option>");
                        type.append("<option value='5'>LessThanOrEqualsTo</option>");
                        break;
                }
            }
            function getConditionBase(condition, type, value) {
                var sheet = spread.getActiveSheet();
                var condtionbase;
                var formula;
                if ((value != null) && (value[0] == "=")) {
                    formula = value;
                    value = null;
                }
                else {
                    formula = null;
                    if (!isNaN(value)) {
                        value = parseFloat(value);
                    }
                }
                switch (condition) {
                    case "0":
                        condtionbase = new spreadNS.CellValueCondition(type, value, formula);
                        break;
                    case "1":
                        condtionbase = new spreadNS.NumberCondition(type, value, formula);
                        break;
                    case "2":
                        condtionbase = new spreadNS.TextCondition(type, value, formula);
                        break;
                    case "3":
                        condtionbase = new spreadNS.ColorCondition(type, value);
                        break;
                    case "4":
                        condtionbase = new spreadNS.FormulaCondition(type, formula);
                        break;
                    case "5":
                        condtionbase = new spreadNS.DateCondition(type, value, formula);
                        break;
                    case "6":
                        if (type < 10) {
                            condtionbase = new spreadNS.DateExCondition(type);
                        }
                        else if (type == 10) {
                            condtionbase = spreadNS.DateExCondition.fromDay(value);
                        } else if (type == 11) {
                            condtionbase = spreadNS.DateExCondition.fromMonth(value);
                        } else if (type == 12) {
                            condtionbase = spreadNS.DateExCondition.fromQuarter(value);
                        } else if (type == 13) {
                            condtionbase = spreadNS.DateExCondition.fromWeek(value);
                        } else {
                            condtionbase = spreadNS.DateExCondition.fromYear(value);
                        }
                        break;
                    case "7":
                        condtionbase = new spreadNS.TextLengthCondition(type, value, formula);
                        break;
                    case "8":
                        var ranges = sheet.getSelections().toArray();
                        condtionbase = new spreadNS.Top10Condition(type, value, ranges);
                        break;
                    default:
                        condtionbase = new spreadNS.CellValueCondition(type, value, formula);
                        break;
                }
                return condtionbase;

            }
            function getConditions() {
                var radio1 = $("#rdoAND").prop("checked");
                var radio2 = $("#rdoOR").prop("checked");

                var condition1 = $("#Condition1").val();
                var condition2 = $("#Condition2").val();
                var type1 = parseInt($("#optEnumType1").val());
                var type2 = parseInt($("#optEnumType2").val());
                var value1 = $("#txtFormulas1").val();
                var value2 = $("#txtFormulas2").val();
                var con1 = getConditionBase(condition1, type1, value1);
                var con2 = getConditionBase(condition2, type2, value2);
                var conditions;
                if (value2 != null || value2 != "" || value2 != undefined) {
                    if (radio1) {
                        conditions = new spreadNS.RelationCondition(spreadNS.RelationCompareType.And, con1, con2);
                    }
                    else if (radio2) {
                        conditions = new spreadNS.RelationCondition(spreadNS.RelationCompareType.Or, con1, con2);
                    }
                    else {
                        conditions = con1;
                    }
                }
                else {
                    conditions = con1;
                }

                return conditions;

            }
            $("#btnHideRowFilter").click(function () {
                var sheet = spread.getActiveSheet();
                var sels = sheet.getSelections();
                if (sels.length == 0) return;
                var sel = sels[0];
                // set filter
                var _drf = new spreadNS.HideRowFilter(sel);
                sheet.rowFilter(_drf);
                var nc = getConditions();
                nc.ignoreBlank = $('#chkIgnoreBlank').attr('checked');
                _drf.addFilterItem(sheet._activeColIndex, nc);

                // filter
                _drf.filter((sel.col >= 0) ? sel.col : 0);
                sheet.invalidateLayout();
                sheet.repaint();
            });

            $("#btnClearFilter").click(function () {
                var sheet = spread.getActiveSheet();
                sheet.rowFilter(null);
                sheet.invalidateLayout();
                sheet.repaint();
            });
        }
        /*code_end*/
    </script>

</head>
<body>
<div class="sample-turtorial">
    <div id="ss" style="width:100%; height:340px;border: 1px solid gray;"></div>
    <div class="demo-options">
        <div class="option-row">
            <label>Conditions:</label>
            <select id="Condition1">
                <option value="0" selected>CellValueCondition</option>
                <option value="1">NumberCondition</option>
                <option value="2">TextCondition</option>
                <option value="3">ColorCondition</option>
                <option value="4">FormulaCondition</option>
                <option value="5">DateCondition</option>
                <option value="6">DateExCondition</option>
                <option value="7">TextLengthCondition</option>
                <option value="8">Top10Condition</option>
            </select>
        </div>
        <div class="option-row">
            <label>CompareType:</label>
            <select id="optEnumType1">
                <option value='0' selected>EqualsTo</option>
                <option value='1'>NotEqualsTo</option>
                <option value='2'>GreaterThan</option>
                <option value='3'>GreaterThanOrEqualsTo</option>
                <option value='4'>LessThan</option>
                <option value='5'>LessThanOrEqualsTo</option>
            </select>
        </div>
        <div class="option-row">
            <label>Compare value or Formula:</label>
            <input id="txtFormulas1" type="text" />
        </div>
        <div class="option-row">
            <label></label>
            <input type="radio" value="And" name="relation" id="rdoAND" />
            <label for="rdoAND" >And</label>
            <input type="radio" value="Or" name="relation" id="rdoOR" />
            <label for="rdoOR">OR</label>
        </div>
        <div class="option-row">
            <label>Conditions:</label>
            <select id="Condition2">
                <option value="0" selected>CellValueCondition</option>
                <option value="1">NumberCondition</option>
                <option value="2">TextCondition</option>
                <option value="3">ColorCondition</option>
                <option value="4">FormulaCondition</option>
                <option value="5">DateCondition</option>
                <option value="6">DateExCondition</option>
                <option value="7">TextLengthCondition</option>
                <option value="8">Top10Condition</option>
            </select>
        </div>
        <div class="option-row">
            <label>CompareType:</label>
            <select id="optEnumType2">
                <option value='0' selected>EqualsTo</option>
                <option value='1'>NotEqualsTo</option>
                <option value='2'>GreaterThan</option>
                <option value='3'>GreaterThanOrEqualsTo</option>
                <option value='4'>LessThan</option>
                <option value='5'>LessThanOrEqualsTo</option>
            </select>
        </div>
        <div class="option-row">
            <label>Compare value or Formula:</label>
            <input id="txtFormulas2" type="text" />
        </div>
        <div class="option-row">
            <label></label>
            <input type="checkbox" id="chkIgnoreBlank" />
            <label for="chkIgnoreBlank">Ignore Blank</label>
        </div>
        <div class="option-row">
            <input type="button" value="Set Filter" id="btnHideRowFilter" />
            <input type="button" value="Clear Filter" id="btnClearFilter" />
        </div>
    </div>
</div>
</body>
</html>
